package com.web.servlet.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.web.servlet.bean.Player;
import com.web.servlet.utils.GenerateNum;
import com.web.servlet.utils.Utils;


public class DBConnect {

	//声明Connection对象
//    private static  Connection con=null;
//    //驱动程序名
//    private static String driver = "com.mysql.jdbc.Driver";
//    //URL指向要访问的数据库名mydata
//    private static String url = "jdbc:mysql://localhost:3306/mydata";
//    //MySQL配置时的用户名
//    private static String user = "root";
//    //MySQL配置时的密码
//    private static String password = "root";
	
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO 自动生成的方法存根
		 
		//DBConnect.queryAllUser();
		//Player player =DBConnect.queryOneUserByUserId("10000");
		//Player player =DBConnect.queryOneUserByUserName("ff","ff");
		//DBConnect.updateUserNikeByUserId("10000","被高兴");
		//Player player =DBConnect.insertOneUser("nihao22","nihao1","很好");
		//DBConnect.updateUserCoinByUserId("10000",2000);
		//System.out.println("userId="+player.getId()+"    userName="+player.getName());
		//DBConnect.deleteUserByUserId("10003");
		
	}
	
	//连接数据库
	public static Connection  connDataBase()
	{
//		 try
//		 {
//	            //加载驱动程序
//	            Class.forName(driver);
//	            //1.getConnection()方法，连接MySQL数据库！！
//	            con = DriverManager.getConnection(url,user,password);
//		 }
//		 catch(ClassNotFoundException e)
//	        {   
//	            //数据库驱动类异常处理
//	            System.out.println("Sorry,can`t find the Driver!");   
//	            e.printStackTrace();   
//	            } catch(SQLException e) {
//	            //数据库连接失败异常处理
//	            e.printStackTrace();  
//	            }catch (Exception e) {
//	            // TODO: handle exception
//	            e.printStackTrace();
//	        }finally{
//	            System.out.println("数据库打开成功！！");
//	        }
		 
		 return DBPoolManager.getConn();
	}
	
	//查询所有用户
	public static List<Player> queryAllUser()
	{
		List<Player> playerList =new ArrayList<Player>();
		try
		{
			Connection con = DBConnect.connDataBase();
            //2.创建statement类对象，用来执行SQL语句！！
            Statement statement = con.createStatement();
            //要执行的SQL语句
            String sql = "select * from User";
            //3.ResultSet类，用来存放获取的结果集！！
            ResultSet rs = statement.executeQuery(sql);  
        
            while(rs.next())
            {
            	Player player =new Player();
            	player.setId(rs.getInt("userId"));
            	player.setName(rs.getString("userName"));
            	player.setNickName(rs.getString("userNikeName"));
            	player.setCoin(rs.getInt("userCoin"));
            	player.setHeadImgIndex(rs.getInt("headImgIndex"));
            	player.setSex(rs.getInt("userSex"));
            	playerList.add(player);
            }
            DBPoolManager.closeConn(con);
           if (rs!=null)
        	   rs.close();
           if (con!=null)
        	   con.close();
           if (statement!=null)
        	   statement.close();
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		
		return playerList;
	}
	
	//查询一个用户通过id
	public static Player queryOneUserByUserId(String userId)
	{
		Player player =null;
		try
		{
            Connection con = DBConnect.connDataBase();
            //2.创建statement类对象，用来执行SQL语句！！
            Statement statement = con.createStatement();
            //要执行的SQL语句
            String sql = "select * from User where userId="+userId;
            //3.ResultSet类，用来存放获取的结果集！！
            ResultSet rs = statement.executeQuery(sql);  
        
            while(rs.next())
            {
            	player =new Player();
            	player.setId(rs.getInt("userId"));
            	player.setName(rs.getString("userName"));
            	player.setNickName(rs.getString("userNikeName"));
            	player.setCoin(rs.getInt("userCoin"));
            	player.setHeadImgIndex(rs.getInt("headImgIndex"));
            	player.setSex(rs.getInt("userSex"));
            	break;
            }
            DBPoolManager.closeConn(con);
            if (rs!=null)
         	   rs.close();
            if (con!=null)
         	   con.close();
            if (statement!=null)
         	   statement.close();
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		
		return player;
	}
	
	//一个用户通过用户名和密码登录，存储过程(pro_UserLogin)
	public static Player loginByUserName(String userName,String password,String strIp)
	{
		Player player =null;
		try
		{
            Connection con = DBConnect.connDataBase();
            
            String currTime =Utils.getCurrTime();
    
            CallableStatement statement =  con.prepareCall("{call pro_UserLogin(?,?,?,?)}");
	    	statement.setString(1, userName);
    		statement.setString(2, password);
    		statement.setString(3, strIp);
    		statement.setString(4, currTime);
    		ResultSet rs= statement.executeQuery();
        
            while(rs.next())
            {
            	player =new Player();
            	player.setId(rs.getInt("userId"));
            	player.setName(rs.getString("userName"));
            	player.setNickName(rs.getString("userNikeName"));
            	player.setCoin(rs.getInt("userCoin"));
            	player.setHeadImgIndex(rs.getInt("headImgIndex"));
            	player.setSex(rs.getInt("userSex"));
            	player.setSex(rs.getInt("userDiamond"));
            	player.setSzWord(rs.getString("userWord"));
            	break;
            }
            DBPoolManager.closeConn(con);
            if (rs!=null)
         	   rs.close();
            if (con!=null)
         	   con.close();
            if (statement!=null)
         	   statement.close();
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		
		return player;
	}
	
	
	//增加一个用户,如果返回player=null表示插入用户失败 存储过程(pro_UserRegister)
	public static boolean insertOneUser(String userName,String password,String userNikeName,String strIp)
	{
		//Player player =null;
		//final int coin=1000;//默认注册用户都给1000金币
		String currTime =Utils.getCurrTime();
		//PreparedStatement psql = null;
		int outReturnValue = 0; // 0 是可以注册，1是产生用户Id被别人使用了，2是用户名已经被注册了
		try
		{
            Connection con = null;
            CallableStatement statement = null;
            con = DBConnect.connDataBase();
            while(true)
            {
            	//随机产生用户Id
                String randomUserId = GenerateNum.generateNumber5_9();
	            statement =  con.prepareCall("{call pro_UserRegister(?,?,?,?,?,?,?)}");
	    		statement.setLong(1, Long.valueOf(randomUserId));
	    		statement.setString(2, userName);
	    		statement.setString(3, password);
	    		statement.setString(4, userNikeName);
	    		statement.setString(5, currTime);
	    		statement.setString(6, strIp);
	    		statement.registerOutParameter(7, java.sql.Types.INTEGER);
	    		ResultSet res= statement.executeQuery();
	    		outReturnValue = statement.getInt(7);
	    		if (outReturnValue==0)
	    		{
	    			break;
	    		}
	    		if(outReturnValue==2)
	    		{	
	    			break;
	    		}
            }
//    		while(res.next())
//    		{
//    			int sum = res.getInt(1);
//    			int ii = res.getInt(1);
//    		}
    		
            statement.close();
    		    		
            /**player = DBConnect.queryOneUserByUserName(userName,password,"");
            if (player!=null)
            {
            	return false;
            }
            
            con = DBConnect.connDataBase();
            //要执行的SQL语句
            String sql = "insert into User (userName, userPassword, userNikeName,userSex,userCoin,userCreateTime,userLoginTime,headImgIndex,userDiamond) values(?,?,?,?,?,?,?,?,?)";
            //预处理添加数据，其中有两个参数--“？”
            psql = con.prepareStatement(sql);
            psql.setString(1, userName);  //用户名    
            psql.setString(2, password); //用户密码
            psql.setString(3, userNikeName);//用户昵称
            psql.setInt(4, 1); //用户性别
            psql.setInt(5, coin); //用户金币
            psql.setString(6, currTime); //用户当前的时间
            psql.setString(7, currTime); //用户当前时间
            psql.setInt(8, 1); //用户头像索引
            psql.setInt(9, 0); //用户钻石

            psql.executeUpdate();           //执行更新
            **/
            
            DBPoolManager.closeConn(con);
            if (con!=null)
	        	   con.close();
           // if (psql!=null)
        //	   psql.close();
            
		}
		catch(Exception e)
		{
			e.printStackTrace();
			return false;
		}
		
		if (outReturnValue==0)
		{
			return true;
		}
		if(outReturnValue==2)
		{	
			return false;
		}
		
		return false;
	}
	
	//更新一个用户登录日期通过id
	public static void updateUserLoginTimeByUserId(String userId)
	{
		try
		{
			String currTime =Utils.getCurrTime();
            Connection con = DBConnect.connDataBase();
            //2.创建statement类对象，用来执行SQL语句！！
            Statement statement = con.createStatement();
            //要执行的SQL语句
            String sql = "update User set userLoginTime="+"'"+currTime+"'"+" where userId="+userId;
            statement.executeUpdate(sql);
            
            DBPoolManager.closeConn(con);
            if (con!=null)
         	   con.close();
            if (statement!=null)
         	   statement.close();
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		 
	}
	
	//更新一个用户昵称通过id
	public static void updateUserNikeByUserId(String userId,String userNikeName)
	{
		try
		{
            Connection con = DBConnect.connDataBase();
            //2.创建statement类对象，用来执行SQL语句！！
            Statement statement = con.createStatement();
            //要执行的SQL语句
            String sql = "update User set userNikeName="+"'"+userNikeName+"'"+" where userId="+userId;
            statement.executeUpdate(sql);
            
            DBPoolManager.closeConn(con);
            if (con!=null)
         	   con.close();
            if (statement!=null)
         	   statement.close();
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		 
	}
	
	//更新一个用户金币通过id
	public static void updateUserCoinByUserId(String userId,int coin)
	{
		try
		{
            Connection con = DBConnect.connDataBase();
            //2.创建statement类对象，用来执行SQL语句！！
            Statement statement = con.createStatement();
            //要执行的SQL语句
            String sql = "update User set userCoin="+coin+" where userId="+userId;
            statement.executeUpdate(sql);
            
            DBPoolManager.closeConn(con);
            if (con!=null)
         	   con.close();
            if (statement!=null)
         	   statement.close();
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		 
	}
	
	//删除一个用户通过id
	public static void deleteUserByUserId(String userId)
	{
		try
		{
            Connection con = DBConnect.connDataBase();
            //2.创建statement类对象，用来执行SQL语句！！
            Statement statement = con.createStatement();
            //要执行的SQL语句
            String sql = "delete from User where userId="+userId;
            statement.execute(sql);
            
            DBPoolManager.closeConn(con);
            if (con!=null)
         	   con.close();
            if (statement!=null)
         	   statement.close();
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		 
	}
	
	//获取平台版本
	public static List<String> getPlatformVersion()
	{
		List<String> list =new ArrayList<String>();
		try
		{
            Connection con = DBConnect.connDataBase();
            //2.创建statement类对象，用来执行SQL语句！！
            Statement statement = con.createStatement();
            //要执行的SQL语句
            String sql = "select * from PlatformVersion";
            //3.ResultSet类，用来存放获取的结果集！！
            ResultSet rs = statement.executeQuery(sql); 
            if (rs.next())
            {
            	 list.add(rs.getString("version"));
            	 list.add(rs.getString("platformDownUrl"));
            	 list.add(rs.getString("androidV"));
            	 list.add(rs.getString("androidDownUrl"));
            	 list.add(rs.getString("iosV"));
            	 list.add(rs.getString("iosDownUrl"));
            }
           
            DBPoolManager.closeConn(con);
            if (rs!=null)
         	   rs.close();
            if (con!=null)
         	   con.close();
            if (statement!=null)
         	   statement.close();
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		
		return list;
	}
	
	//插入反馈信息
	public static boolean insertFeedBacK(String userId,String content)
	{
		String currTime =Utils.getCurrTime();
		PreparedStatement psql = null;
		try
		{
            Connection con = DBConnect.connDataBase();
            //要执行的SQL语句
            String sql = "insert into Feedback (userId, content,fTime) values(?,?,?)";
            //预处理添加数据，其中有两个参数--“？”
            psql = con.prepareStatement(sql);
            psql.setString(1, userId);      
            psql.setString(2, content);
            psql.setString(3, currTime);
            psql.executeUpdate();           //执行更新
            
            DBPoolManager.closeConn(con);
            if (con!=null)
	        	   con.close();
            if (psql!=null)
        	   psql.close();
            
		}
		catch(Exception e)
		{
			e.printStackTrace();
			return false;
		}
		
		return true;
	}
	
}
